﻿using System;
using System.Data.SqlServerCe;
using System.Linq;
using System.Collections.Generic;
using System.Reflection;
using System.Text;
using System.Windows.Forms;
using SenseTimeTracker.BusinessLayer;
using SenseTimeTracker.Holidays;
using SenseTimeTracker.Main;
using SenseTimeTracker.Misc;
using SenseTimeTracker.Properties;
using StedySoft.SenseSDK;

namespace SenseTimeTracker.Reports
{
	public class ReportsSliderPanel : SliderPanel
	{
		private SensePanelComboItem reportPeriodItem;
		private MonthlyProjectTotalReportDisplayItem frameTotalItem;
		private DateTime reportPeriod = DateTime.Now
			;

		public ReportsSliderPanel(SenseTimeTrackerForm form)
			: base(form, "ReportsSmallIcon.png", "ReportsLargeIcon.png")
		{
			menuItem = new MenuItem();
			menuItem.Text = Resources.MenuItemMenu;
			menuItem.Popup += menuItem_Popup;

			List<MenuItem> menuLevels = new List<MenuItem>();
			int currentLevel = 0;
			menuLevels.Add(menuItem);

			foreach (KeyValuePair<object, string> reportType in EnumExtension.GetValues(typeof(ReportType)))
			{
				ReportTypeMenuItem reportTypeMenuItem = new ReportTypeMenuItem();
				reportTypeMenuItem.Text = reportType.Value;
				reportTypeMenuItem.ReportType = (ReportType)reportType.Key;
				reportTypeMenuItem.Click += reportTypeMenuItem_Click;
				int menuLevel = EnumExtension.Level(reportType.Key);
				if (menuLevel > currentLevel)
				{
					MenuItem subMenuItem = menuLevels[currentLevel].MenuItems[menuLevels[currentLevel].MenuItems.Count - 1];
					subMenuItem.Popup += menuItem_Popup;
					menuLevels.Add(subMenuItem);
				}

				if (menuLevel < currentLevel)
				{
					menuLevels.RemoveAt(currentLevel);
				}

				currentLevel = menuLevel;
				menuLevels[currentLevel].MenuItems.Add(reportTypeMenuItem);
			}

			reportPeriodItem = new SensePanelComboItem();
			reportPeriodItem.LabelText = string.Format("{0}:", Resources.ReportPeriod);
			reportPeriodItem.OnSelectedValueChanged += reportPeriodItem_OnSelectedValueChanged;
			reportPeriodItem.ShowSeparator = true;

			frameTotalItem = new MonthlyProjectTotalReportDisplayItem();

		}

		void reportPeriodItem_OnSelectedValueChanged(object Sender, object Value)
		{
			if (reportPeriodItem.SelectedIndex != 3)
			{
				reportPeriod = (DateTime) Value;
				Bind(senseListControl);
			}
		}

		void reportTypeMenuItem_Click(object sender, EventArgs e)
		{
			Persistance.Instance.ReportType = ((ReportTypeMenuItem) sender).ReportType;
			Bind(senseListControl);
		}

		void menuItem_Popup(object sender, EventArgs e)
		{
			foreach (ReportTypeMenuItem menuItem in ((MenuItem)sender).MenuItems)
			{
				if (menuItem.MenuItems.Count == 0)
				{
					menuItem.Checked = menuItem.ReportType == Persistance.Instance.ReportType;
				}
			}
		}

		public override string Name { get { return Resources.PanelReportsName; } }



		public override void Bind(SenseListControl senseListControl)
		{
			base.Bind(senseListControl);

			switch (Persistance.Instance.ReportType)
			{
				case ReportType.Total:
					BindTotalReport();
					break;
				case ReportType.Project:
					BindProjectReport();
					break;
				case ReportType.UtilisationByClient:
					BindUtilisationByClientReport();
					break;
				case ReportType.UtilisationByProject:
					BindUtilisationByProjectReport();
					break;
				case ReportType.UtilisationByTask:
					BindUtilisationByTaskReport();
					break;
				default:
					throw new NotSupportedException();
			}
		}

		private void BindUtilisationByClientReport()
		{
			SqlCeCommand command = new SqlCeCommand(
				"SELECT clientName, SUM(usageTime) AS usageTime, SUM(taskEstimate) AS taskEstimate FROM (" +
				"SELECT Clients.clientName, " +
				"SUM(DATEDIFF(Minute, TimeRecords.timeRecordStartTime, TimeRecords.timeRecordFinalTime)) AS usageTime, " +
				"AVG(Tasks.taskEstimate) AS taskEstimate " +
				"FROM Clients " +
				"INNER JOIN Projects ON Projects.clientId = Clients.clientId " +
				"INNER JOIN Tasks ON Tasks.projectId = Projects.projectId " +
				"INNER JOIN TimeRecords ON TimeRecords.taskId = Tasks.taskId " +
				"WHERE TimeRecords.personId = @personId AND Tasks.taskEstimate > 0" +
				"GROUP BY Clients.clientName, Tasks.taskId) AS T GROUP BY clientName",
				DatabaseManager.DbConnection);
			command.Parameters.Add("@personId", Persistance.Instance.UserId);
			DatabaseManager.DbConnection.Open();
			SqlCeDataReader reader = command.ExecuteReader();
			while (reader.Read())
			{
				senseListControl.AddItem(new UtilisationDisplayItem
				{
					Label = reader["clientName"].ToString(),
					UtilisationCurrent = float.Parse(reader["usageTime"].ToString()) / 60.0f,
					UtilisationLimit = float.Parse(reader["taskEstimate"].ToString()),
				});
			}

			DatabaseManager.DbConnection.Close();
		}

		private void BindUtilisationByProjectReport()
		{
			SqlCeCommand command = new SqlCeCommand(
				"SELECT projectName, SUM(usageTime) AS usageTime, SUM(taskEstimate) AS taskEstimate FROM (" +
				"SELECT Projects.projectName, " +
				"SUM(DATEDIFF(Minute, TimeRecords.timeRecordStartTime, TimeRecords.timeRecordFinalTime)) AS usageTime, " +
				"AVG(Tasks.taskEstimate) AS taskEstimate " +
				"FROM Projects " +
				"INNER JOIN Tasks ON Tasks.projectId = Projects.projectId " +
				"INNER JOIN TimeRecords ON TimeRecords.taskId = Tasks.taskId " +
				"WHERE TimeRecords.personId = @personId AND Tasks.taskEstimate > 0" +
				"GROUP BY Projects.projectName, Tasks.taskId) AS T GROUP BY projectName",
				DatabaseManager.DbConnection);
			command.Parameters.Add("@personId", Persistance.Instance.UserId);
			DatabaseManager.DbConnection.Open();
			SqlCeDataReader reader = command.ExecuteReader();
			while (reader.Read())
			{
				senseListControl.AddItem(new UtilisationDisplayItem
				{
					Label = reader["projectName"].ToString(),
					UtilisationCurrent = float.Parse(reader["usageTime"].ToString()) / 60.0f,
					UtilisationLimit = float.Parse(reader["taskEstimate"].ToString()),
				});
			}

			DatabaseManager.DbConnection.Close();
		}

		private void BindUtilisationByTaskReport()
		{
			SqlCeCommand command = new SqlCeCommand(
				"SELECT taskName, SUM(usageTime) AS usageTime, SUM(taskEstimate) AS taskEstimate FROM (" +
				"SELECT Tasks.taskName, " +
				"SUM(DATEDIFF(Minute, TimeRecords.timeRecordStartTime, TimeRecords.timeRecordFinalTime)) AS usageTime, " +
				"AVG(Tasks.taskEstimate) AS taskEstimate " +
				"FROM Tasks " +
				"INNER JOIN TimeRecords ON TimeRecords.taskId = Tasks.taskId " +
				"WHERE TimeRecords.personId = @personId AND Tasks.taskEstimate > 0" +
				"GROUP BY Tasks.taskName) AS T GROUP BY taskName",
				DatabaseManager.DbConnection);
			command.Parameters.Add("@personId", Persistance.Instance.UserId);
			DatabaseManager.DbConnection.Open();
			SqlCeDataReader reader = command.ExecuteReader();
			while (reader.Read())
			{
				senseListControl.AddItem(new UtilisationDisplayItem
				{
					Label = reader["taskName"].ToString(),
					UtilisationCurrent = float.Parse(reader["usageTime"].ToString()) / 60.0f,
					UtilisationLimit = float.Parse(reader["taskEstimate"].ToString()),
				});
			}

			DatabaseManager.DbConnection.Close();
		}

		private void BindTotalReport()
		{
			SqlCeCommand command = new SqlCeCommand(
				"SELECT Projects.projectId, Projects.projectName, timeRecordStartTime, timeRecordFinalTime, Tasks.taskCategoryId, taskCategoryName " +
				"FROM TimeRecords " +
				"INNER JOIN Tasks ON Tasks.taskId = TimeRecords.taskId " +
				"INNER JOIN Projects ON Projects.projectId = Tasks.projectId " +
				"INNER JOIN TaskCategories ON Tasks.taskCategoryId = TaskCategories.taskCategoryId " +
				"WHERE TimeRecords.personId = @personId " +
				"ORDER BY timeRecordStartTime",
				DatabaseManager.DbConnection);
			command.Parameters.Add("@personId", Persistance.Instance.UserId);
			DatabaseManager.DbConnection.Open();
			SqlCeDataReader reader = command.ExecuteReader();
			DateTime date = DateTime.MinValue;
			Dictionary<Guid, SaldoReportDisplayItem.CategoryTotalValuePair> categoryTotals = null;
			List<SaldoReportDisplayItem> items = new List<SaldoReportDisplayItem>();
			TimeSpan overTimeSaldo = TimeSpan.Zero;
			Dictionary<DateTime, double> requiredTimes = null;

			while (reader.Read())
			{
				DateTime startTime = (DateTime)reader["timeRecordStartTime"];
				DateTime finalTime = (DateTime)reader["timeRecordFinalTime"];

				if (startTime.Month != date.Month || date == DateTime.MinValue)
				{
					if (date != DateTime.MinValue)
					{
						items.Add(AddItem(date, categoryTotals, ref overTimeSaldo, requiredTimes));
					}

					categoryTotals = new Dictionary<Guid, SaldoReportDisplayItem.CategoryTotalValuePair>();
					requiredTimes = Calendar.Estimation(new DateTime(startTime.Year, startTime.Month, 1));
				}

				date = startTime;
				requiredTimes[startTime.Date] = 0;
				requiredTimes[startTime.Date] = 0;

				Category taskCategory = new Category
				{
					Id = (Guid)reader["taskCategoryId"],
					Name = reader["taskCategoryName"].ToString(),
				};

				if (!categoryTotals.ContainsKey(taskCategory.Id))
				{
					categoryTotals.Add(taskCategory.Id, new SaldoReportDisplayItem.CategoryTotalValuePair
					{
						TaskCategory = taskCategory,
						CategoryTotal = TimeSpan.Zero,
					});
				}

				categoryTotals[taskCategory.Id].CategoryTotal += finalTime - startTime;
			}

			if (date != DateTime.MinValue)
			{
				items.Add(AddItem(date, categoryTotals, ref overTimeSaldo, requiredTimes));
			}

			DatabaseManager.DbConnection.Close();

			var filteredItems = from item in items orderby item.Date descending select item;
			foreach (SaldoReportDisplayItem item in filteredItems)
			{
				senseListControl.AddItem(item);
			}
		}

		private void BindProjectReport()
		{
			reportPeriodItem.Items.Clear();
			for (int i = -3; i <= 3; i++)
			{
				DateTime datePeriod = new DateTime(reportPeriod.Year, reportPeriod.Month, 1).AddMonths(i);
				reportPeriodItem.Items.Add(new SensePanelComboItem.Item(datePeriod.ToString("MMMM yyyy"), datePeriod));
			}

			reportPeriodItem.SelectedIndex = 3;
			senseListControl.TopIndex = 0;
			senseListControl.Clear();
			senseListControl.AddItem(reportPeriodItem);
			senseListControl.AddItem(frameTotalItem);
			senseListControl.AddItem(new SensePanelDividerItem(string.Empty, Resources.ReportProjectsDeviderText));
			DateTime startPeriod = (DateTime)reportPeriodItem.SelectedValue;
			Dictionary<DateTime, double> estimation = Calendar.Estimation(startPeriod);
			TimeSpan requiredTotal = TimeSpan.Zero;
			foreach (double time in estimation.Values)
			{
				requiredTotal += TimeSpan.FromHours(time);
			}

			SqlCeCommand command = new SqlCeCommand(
				"SELECT Projects.projectId, Projects.projectName, timeRecordStartTime, timeRecordFinalTime FROM TimeRecords " +
				"INNER JOIN Tasks ON Tasks.taskId = TimeRecords.taskId " +
				"INNER JOIN Projects ON Projects.projectId = Tasks.projectId " +
				"WHERE TimeRecords.personId = @personId " +
				"AND (timeRecordStartTime >= @startTime OR timeRecordFinalTime >= @startTime) " +
				"AND timeRecordStartTime < @finalTime " +
				"ORDER BY Projects.projectId, timeRecordStartTime",
				DatabaseManager.DbConnection);
			command.Parameters.Add("@personId", Persistance.Instance.UserId);
			command.Parameters.Add("@startTime", startPeriod);
			command.Parameters.Add("@finalTime", startPeriod.AddMonths(1));
			DatabaseManager.DbConnection.Open();
			SqlCeDataReader reader = command.ExecuteReader();
			Guid projectId = Guid.Empty;
			string projectName = string.Empty;
			TimeSpan projectTotal = TimeSpan.Zero;
			TimeSpan frameTotal = TimeSpan.Zero;
			List<ReportDisplayItem> items = new List<ReportDisplayItem>();
			while (reader.Read())
			{
				Guid nextProjectId = new Guid(reader["projectId"].ToString());
				if (nextProjectId != projectId)
				{
					if (projectId != Guid.Empty)
					{
						ReportDisplayItem item = new ReportDisplayItem();
						item.ProjectName = projectName;
						item.ProjectTotalTime = projectTotal;
						item.ProjectId = projectId;
						items.Add(item);
					}

					projectTotal = TimeSpan.Zero;
					projectName = reader["projectName"].ToString();
					projectId = new Guid(reader["projectId"].ToString());
				}

				DateTime startTime = (DateTime)reader["timeRecordStartTime"];
				DateTime finalTime = (DateTime)reader["timeRecordFinalTime"];
				TimeSpan taskTotal = finalTime - startTime;
				frameTotal += taskTotal;
				projectTotal += taskTotal;

				estimation[startTime.Date] = 0;
				estimation[finalTime.Date] = 0;
			}

			if (projectId != Guid.Empty)
			{
				ReportDisplayItem item = new ReportDisplayItem();
				item.ProjectName = projectName;
				item.ProjectTotalTime = projectTotal;
				item.ProjectId = projectId;
				items.Add(item);
			}

			TimeSpan overTimeEstimation = Estimation(estimation);
			DatabaseManager.DbConnection.Close();
			frameTotalItem.FrameTotal = frameTotal;
			frameTotalItem.FrameRequired = requiredTotal;
			frameTotalItem.FrameEstimated = frameTotal + overTimeEstimation;
			frameTotalItem.FrameOvertime = frameTotal - requiredTotal + overTimeEstimation;

			var filteredItems = from item in items select item;
			foreach (ReportDisplayItem item in filteredItems)
			{
				senseListControl.AddItem(item);
			}
		}

		private TimeSpan Estimation(Dictionary<DateTime, double> estimation)
		{
			TimeSpan calculatedEstimation = TimeSpan.Zero;
			foreach (DateTime date in estimation.Keys)
			{
				if (date >= DateTime.Now.Date)
				{
					calculatedEstimation += TimeSpan.FromHours(estimation[date]);
				}
			}

			return calculatedEstimation;
		}

		private SaldoReportDisplayItem AddItem(DateTime date, Dictionary<Guid, SaldoReportDisplayItem.CategoryTotalValuePair> categoryTotals, ref TimeSpan saldo, Dictionary<DateTime, double> requiredTimes)
		{
			Dictionary<DateTime, double> estimation = Calendar.Estimation(new DateTime(date.Year, date.Month, 1));
			TimeSpan requiredTotal = TimeSpan.Zero;
			foreach (double time in estimation.Values)
			{
				requiredTotal += TimeSpan.FromHours(time);
			}

			TimeSpan totalTime = TimeSpan.Zero;
			foreach (SaldoReportDisplayItem.CategoryTotalValuePair time in categoryTotals.Values)
			{
				totalTime += time.CategoryTotal;
			}

			saldo += totalTime - requiredTotal;

			TimeSpan overTimeEstimation = Estimation(requiredTimes);

			return new SaldoReportDisplayItem
			{
				Date = date,
				CategoryTotals = categoryTotals,
				Total = totalTime,
				Required = requiredTotal,
				Overtime = totalTime - requiredTotal + overTimeEstimation,
				Saldo = saldo + overTimeEstimation,
			};
		}
	}
}
